package com.matt.foundation.sqlOperator;

import java.sql.*;
import java.util.Date;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/7
 * *******************************
 */
public class TeachingPlanTransfer {
    public static void main(String[] args) throws Exception{
//        transferMaterialData();
//        transferPlanData();

    }

    public static void transferMaterialData() {
        int num = 0;
        String remoteUrl = "jdbc:mysql://dev.mattcd.com:3306/matt_app";
        String remoteName = "root";
        String remotePWd = "FQs088";

        String localUrl = "jdbc:mysql://localhost:3306/vega";
        String localName = "root";
        String localPWd = "123456";
        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select * from M_TestStore limit 0, 3000";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(localUrl, localName, localPWd);
            PreparedStatement localPreparedStatement = null;
            ResultSet localResultSet = null;
            Integer count=0;
            while (remoteResultSet.next()){
                Integer id = remoteResultSet.getInt(1); //id
                String title = remoteResultSet.getString(2); //题目
                String media = remoteResultSet.getString(3); //视频或音频地址
                String thumb =  remoteResultSet.getString(4); //预览图
                if (thumb != null){
                    thumb = "info/" + thumb;
                }

                String content = remoteResultSet.getString(5); //教案内容

                if (content.contains("http://app.mattservice.com/")){
                    content = content.replaceAll("http://app.mattservice.com/", "http://appimg.mattservice.com/");
//                    System.err.println("ok");
                }

                if (content.contains("<img src=\"/")){
                    System.out.println(++count);
//                    System.out.println(content);
                    content = content.replaceAll("<img src=\"/", "<img src=\"http://appimg.mattservice.com/");
//                    System.out.println(content);
                }

                if (content.contains("<img alt=\"\" src=\"/")){
                    content = content.replaceAll("<img alt=\"\" src=\"/", "<img alt=\"\" src=\"http://appimg.mattservice.com/");
                    System.out.println(++count);
                }
                Date createDate = remoteResultSet.getObject(6) == null? null : ( (Date) remoteResultSet.getObject(6)); //传教时间
                Integer type = remoteResultSet.getInt(7); //4菜谱
                if(type == 1){
                    //行为
                }else if(type == 2){
                    //游戏
                }else if(type ==3){
                    //儿保
                }else if(type == 100){
                    //月子餐
                }

                //8跳过，ApiGroupID
                //9跳过，Tools
//                String img = rs.getString(10);//图片

//                Integer month = rs.getInt(16);//月份
//                System.out.println("id :" + id);
//                System.out.println("title :" +title);
//                System.out.println("media :" +media);
//                System.out.println("thumb :" + thumb);
                System.out.println("content :" + content);
                System.out.println(content.length());
//                System.out.println("createDate :" + createDate);
//                System.out.println("img :" + img);
//                System.out.println("month :" + month);


                String insert = "insert into teaching_material(id, title, media, thumb, content, created) values( ?, ?, ?, ?, ?, ?)";

                localPreparedStatement = localConn.prepareStatement(insert);


                localPreparedStatement.setLong(1, id);
                localPreparedStatement.setString(2, title);
                localPreparedStatement.setString(3, media);
                localPreparedStatement.setString(4, thumb);
                localPreparedStatement.setString(5, content);
                localPreparedStatement.setDate(6, new java.sql.Date(createDate.getTime()));
                localPreparedStatement.execute();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void transferPlanData() {
        int num = 0;
        String remoteUrl = "jdbc:mysql://dev.mattcd.com:3306/matt_app";
        String remoteName = "root";
        String remotePWd = "FQs088";

        String localUrl = "jdbc:mysql://localhost:3306/vage";
        String localName = "root";
        String localPWd = "123456";

        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select b.*, s.title from M_BabyDefaultHandbook b LEFT JOIN M_TestStore s ON b.TestStore_ID = s.id WHERE (Feed_Type =2 OR Feed_Type =3) AND Order_Money =15888 limit 0, 40000";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(localUrl, localName, localPWd);
            PreparedStatement localPreparedStatement = null;
            ResultSet localResultSet = null;
            while (remoteResultSet.next()){
                Integer id = remoteResultSet.getInt(1); //id
                Integer month = remoteResultSet.getInt(2); //月份
                Integer day = remoteResultSet.getInt(3); //天数
                Long materialId =  remoteResultSet.getLong(4); //对应资料id
                Long created = remoteResultSet.getLong(6);//创建时间
                Time fromTime = remoteResultSet.getTime(7);//创建时间
                Time toTime = remoteResultSet.getTime(8);//创建时间

                String title = remoteResultSet.getString(12);//题目

                //8跳过，ApiGroupID
                //9跳过，Tools
//                String img = rs.getString(10);//图片

//                Integer month = rs.getInt(16);//月份
                System.out.println("id :" + id);
                System.out.println("month :" +month);
                System.out.println("day :" +day);
                System.out.println("materialId :" + materialId);
                System.out.println("created :" + created);
                System.out.println("fromTime :" + fromTime);
                System.out.println("toTime :" + toTime);
                System.out.println("title :" + title);
//                System.out.println("img :" + img);
//                System.out.println("month :" + month);


                String insert = "insert into teachingPlan(id, month, day, materialId, materialTitle, created, fromTime, toTime, isDeleted) values( ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                localPreparedStatement = localConn.prepareStatement(insert);

                localPreparedStatement.setLong(1, id);
                localPreparedStatement.setInt(2, month);
                localPreparedStatement.setInt(3, day);
                localPreparedStatement.setLong(4, materialId);
                localPreparedStatement.setString(5, title);
                localPreparedStatement.setDate(6, new java.sql.Date(created*1000));
                localPreparedStatement.setTime(7, fromTime);
                localPreparedStatement.setTime(8, toTime);
                localPreparedStatement.setInt(9, 0);//是否删除
                localPreparedStatement.execute();
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
